﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL;
using KPIS.GERP.GHRMS.DALFactory;
using KPIS.DBM;


namespace KPIS.GERP.GHRMS.BLL
{
    public class InitialData_BLL
    {

        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        //public DataSet ListTrainingSubject(string seq)
        //{
        //    dbManager.CreateParameters(1);
        //    dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

        //    DataSet ds = new DataSet();
        //    dbManager.Open();
        //    string sql = "select *,training_subject_seq as Sequence from hrm_training_subjects";
        //    sql += " where record_status not like 'D'";
        //    if (seq != "")
        //    {
        //        sql += " and training_subject_seq = " + seq;
        //    }
        //    ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //    dbManager.Dispose();
        //    return ds;
        //}



        //public void DeleteTrainingSubject(int Sequence)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(2);
        //        dbManager.AddParameters(0, "@sequence", Sequence);
        //        dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
        //        //dbManager.AddParameters(2, "@updated_by", UpdateBy);
        //        //dbManager.AddParameters(3, "@updated_when", DateTime.Today);
        //        dbManager.Open();
        //        dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_subjects SET RECORD_STATUS=@record_status WHERE training_subject_seq=@sequence");
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}




        //public void InsertTrainingSubject(string training_subject_descr,string created_by )
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(2);
        //        dbManager.AddParameters(0, "@training_subject_descr", training_subject_descr);
        //        dbManager.AddParameters(1, "@record_status", SystemInfo._ActiveRecordStatus);
        //        dbManager.AddParameters(2, "@created_by", created_by);
        //        dbManager.AddParameters(3, "@created_when", DateTime.Today);                

        //        dbManager.Open();
        //        string sql;
        //        sql = "INSERT INTO hrm_training_subjects (";
        //        sql += "training_subject_descr";
        //        sql += "created_by";
        //        sql += "created_when";
        //        sql += ",RECORD_STATUS";
        //        sql += ") VALUES (";
        //        sql += "@training_subject_descr";
        //        sql += "@created_by";
        //        sql += "@created_when";
        //        sql += ",@record_status)";

        //        dbManager.ExecuteNonQuery(CommandType.Text, sql);
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}


        //public void UpdateTrainingSubject(string Sequence, string training_subject_descr, string UpdateBy)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(4);
        //        dbManager.AddParameters(0, "@sequence", Sequence);
        //        dbManager.AddParameters(1, "@training_subject_descr", training_subject_descr);
        //        dbManager.AddParameters(2, "@updated_by", UpdateBy);
        //        dbManager.AddParameters(3, "@updated_when", DateTime.Today);
        //        dbManager.Open();
                
        //        dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_subjects SET training_subject_descr=@training_subject_descr WHERE training_subject_seq=@sequence");
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}



        //public DataSet ListTrainingSubjectDtls(string seq, string training_subject_seq)
        //{
        //    dbManager.CreateParameters(1);
        //    dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

        //    DataSet ds = new DataSet();
        //    dbManager.Open();
        //    string sql = "select *,training_subject_dtls_seq as Sequence from hrm_training_subjects_dtls";
        //    sql += " where record_status not like 'D' and training_subject_seq = " + training_subject_seq;
        //    if (seq != "")
        //    {
        //        sql += " and training_subject_seq = " + seq;
        //    }
        //    ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //    dbManager.Dispose();
        //    return ds;
        //}



        //public void DeleteTrainingSubjectDtls(int Sequence)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(2);
        //        dbManager.AddParameters(0, "@sequence", Sequence);
        //        dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
        //        //dbManager.AddParameters(2, "@updated_by", UpdateBy);
        //        //dbManager.AddParameters(3, "@updated_when", DateTime.Today);
        //        dbManager.Open();
        //        dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_subjects_dtls SET RECORD_STATUS=@record_status WHERE training_subject_dtls_seq=@sequence");
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}


        //public void InsertTrainingSubjectDtls(string training_subject_seq, string topic_descr, string detai)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(4);
        //        dbManager.AddParameters(0, "@training_subject_seq", training_subject_seq);
        //        dbManager.AddParameters(1, "@topic_descr", topic_descr);
        //        dbManager.AddParameters(2, "@detail", detai);
        //        dbManager.AddParameters(3, "@record_status", SystemInfo._ActiveRecordStatus);

        //        dbManager.Open();
        //        string sql;
        //        sql = "INSERT INTO hrm_training_subjects_dtls (";
        //        sql += "training_subject_seq";
        //        sql += ",topic_descr";
        //        sql += ",detail";
        //        sql += ",RECORD_STATUS";
        //        sql += ") VALUES (";
        //        sql += "@training_subject_seq";
        //        sql += ",@topic_descr";
        //        sql += ",@detail";
        //        sql += ",@record_status)";

        //        dbManager.ExecuteNonQuery(CommandType.Text, sql);
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}


        //public void UpdateTrainingSubjectDtls(string Sequence, string topic_descr, string detai)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(3);
        //        dbManager.AddParameters(0, "@sequence", Sequence);
        //        dbManager.AddParameters(1, "@topic_descr", topic_descr);
        //        dbManager.AddParameters(2, "@detail", detai);
        //        dbManager.Open();

        //        dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_subjects_dtls SET topic_descr=@topic_descr, detail=@detail WHERE training_subject_dtls_seq=@sequence");
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}



        public DataSet ListTrainingCompareGroup(string seq)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            string sql = "select *,compare_group_seq as Sequence from hrm_training_compare_groups where RECORD_STATUS not like 'D'";
            if (seq != "")
            {
                sql += " AND compare_group_seq = " + seq;
            }
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }

        public void DeleteTrainingCompareGroup(string Sequence)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                //dbManager.AddParameters(2, "@updated_by", UpdateBy);
                //dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_compare_groups SET RECORD_STATUS=@record_status WHERE compare_group_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void InsertTrainingCompareGroup(string compare_group_code, string compare_group_descr, string created_by)
        {
            try
            {
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@compare_group_code", compare_group_code);
                dbManager.AddParameters(1, "@compare_group_descr", compare_group_descr);
                dbManager.AddParameters(2, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(3, "@created_by", created_by);
                dbManager.AddParameters(4, "@created_when", DateTime.Today);                


                dbManager.Open();
                string sql;
                sql = "INSERT INTO hrm_training_compare_groups (";
                sql += "compare_group_code";
                sql += ",compare_group_descr";
                sql += ",created_by";
                sql += ",created_when";
                sql += ",RECORD_STATUS";
                sql += ") VALUES (";
                sql += "@compare_group_code";
                sql += ",@compare_group_descr";
                sql += ",@created_by";
                sql += ",@created_when";
                sql += ",@record_status)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(3);
        //        dbManager.AddParameters(0, "@compare_group_seq", compare_group_seq);
        //        dbManager.AddParameters(1, "@compare_group_descr", compare_group_descr);
        //        dbManager.AddParameters(2, "@record_status", SystemInfo._ActiveRecordStatus);

        //        dbManager.Open();
        //        string sql;
        //        sql = "INSERT INTO hrm_training_compare_groups (";
        //        sql += "compare_group_seq";
        //        sql += ",compare_group_descr";
        //        sql += ",RECORD_STATUS";
        //        sql += ") VALUES (";
        //        sql += "@compare_group_seq";
        //        sql += ",@compare_group_descr";
        //        sql += ",@record_status)";

        //        dbManager.ExecuteNonQuery(CommandType.Text, sql);
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}



        public void UpdateTrainingCompareGroup(string Sequence, string compare_group_code,
                                               string compare_group_descr, string updated_by)
        {
            try
            {
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@compare_group_code", compare_group_code);
                dbManager.AddParameters(1, "@sequence", Sequence);
                dbManager.AddParameters(2, "@compare_group_descr", compare_group_descr);
                dbManager.AddParameters(3, "@updated_by", updated_by);
                dbManager.AddParameters(4, "@updated_when", DateTime.Today);
                dbManager.Open();

                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_compare_groups SET compare_group_code = @compare_group_code , compare_group_descr=@compare_group_descr , updated_by=@updated_by  WHERE compare_group_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(2);
        //        dbManager.AddParameters(0, "@sequence", Sequence);
        //        dbManager.AddParameters(1, "@compare_group_descr", compare_group_descr);
        //        dbManager.Open();

        //        dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_compare_groups SET compare_group_seq = txtid ,compare_group_descr=@compare_group_descr WHERE compare_group_seq=@sequence");
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}

        public Boolean chkDupTrainingCompareGroup(string compare_group_code)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "select * from hrm_training_compare_groups"
                    + " where compare_group_code = '" + compare_group_code + "'"
                    + " and record_status='" + SystemInfo._ActiveRecordStatus + "'";
                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }

            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public DataSet List(string seq)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            string sql = "select *,compare_group_seq as Sequence from hrm_training_compare_groups where RECORD_STATUS not like 'D'";
            if (seq != "")
            {
                sql += " AND compare_group_seq = " + seq;
            }
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }

        public DataSet ListBgmSource(string seq)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            string sql = "SELECT bgm_mas_source.bgm_source_seq"
                        +", bgm_mas_source.bgm_source_name"
                        +" FROM bgm_mas_source"
                        + " WHERE bgm_mas_source.record_status not like @RecordStatus";
            if (seq != "")
            {
                sql += " AND bgm_source_seq = " + seq;
            }

            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        } 

        public void DeleteScholarshipFund(int Sequence)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                //dbManager.AddParameters(2, "@updated_by", UpdateBy);
                //dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_scholarship_funds SET RECORD_STATUS=@record_status WHERE scholar_fund_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void InsertScholarshipFund(string scholar_fund_descr)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@scholar_fund_descr", scholar_fund_descr);
                dbManager.AddParameters(1, "@record_status", SystemInfo._ActiveRecordStatus);

                dbManager.Open();
                string sql;
                sql = "INSERT INTO hrm_scholarship_funds (";
                sql += "scholar_fund_descr";
                sql += ",RECORD_STATUS";
                sql += ") VALUES (";
                sql += "@scholar_fund_descr";
                sql += ",@record_status)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void UpdateScholarshipFund(string Sequence, string scholar_fund_descr)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@scholar_fund_descr", scholar_fund_descr);
                dbManager.Open();

                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_scholarship_funds SET scholar_fund_descr=@scholar_fund_descr WHERE scholar_fund_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

        public DataSet ListTrainingTypeCategories(string seq)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            string sql = "select *,training_cat_seq as Sequence from hrm_training_type_categories";
            sql += " where record_status not like 'D'";
            if (seq != "")
            {
                sql += " and training_cat_seq = " + seq;
            }
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }
        //{
        //    dbManager.CreateParameters(1);
        //    dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

        //    DataSet ds = new DataSet();
        //    dbManager.Open();
        //    string sql = "select *,training_cat_seq as Sequence from hrm_training_type_categories";
        //    if (seq != "")
        //    {
        //        sql += " where training_cat_seq = " + seq;
        //    }
        //    ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //    dbManager.Dispose();
        //    return ds;
        //}

        public void DeleteTrainingTypeCategories(int Sequence)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                //dbManager.AddParameters(2, "@updated_by", UpdateBy);
                //dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "delete from hrm_training_type_categories WHERE training_cat_seq = @sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void InsertTrainingTypeCategories(string training_cat_descr, string created_by)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@training_cat_descr", training_cat_descr);
                dbManager.AddParameters(1, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(2, "@created_by", created_by);
                dbManager.AddParameters(3, "@created_when", DateTime.Today);

                dbManager.Open();
                string sql;
                sql = "INSERT INTO hrm_training_type_categories (";
                sql += "training_cat_descr";
                sql += ",created_by";
                sql += ",created_when";
                sql += ",RECORD_STATUS";
                sql += ") VALUES (";
                sql += "@training_cat_descr";
                sql += ",@created_by"; 
                sql += ",@created_when";
                sql += ",@record_status)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

        //{
        //    try
        //    {
        //        dbManager.CreateParameters(2);
        //        dbManager.AddParameters(0, "@training_cat_descr", training_cat_descr);
        //        dbManager.AddParameters(1, "@record_status", SystemInfo._ActiveRecordStatus);

        //        dbManager.Open();
        //        string sql;
        //        sql = "INSERT INTO hrm_training_type_categories (";
        //        sql += "training_cat_descr";
        //        sql += ",RECORD_STATUS";
        //        sql += ") VALUES (";
        //        sql += "@training_cat_descr";
        //        sql += ",@record_status)";

        //        dbManager.ExecuteNonQuery(CommandType.Text, sql);
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}


        public void UpdateTrainingTypeCategories(string Sequence, string training_cat_descr, string updated_by)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@training_cat_descr", training_cat_descr);
                dbManager.AddParameters(2, "@updated_by", updated_by);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();

                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_type_categories SET training_cat_descr=@training_cat_descr,updated_by=@updated_by  WHERE training_cat_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

        //{
        //    try
        //    {
        //        dbManager.CreateParameters(2);
        //        dbManager.AddParameters(0, "@sequence", Sequence);
        //        dbManager.AddParameters(1, "@training_cat_descr", training_cat_descr);
        //        dbManager.Open();

        //        dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_type_categories SET training_cat_descr=@training_subject_descr WHERE training_cat_seq=@sequence");
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}

        public DataSet ListTrainingGroup(string seq)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            string sql = "select *,training_group_seq as Sequence"
                    + ", case when hrm_training_groups.internal = 1 then 'บุคคลากรภายใน' else 'บุคคลากรภายนอก' end as n_internal"
                    + " from hrm_training_groups"
                    + " where record_status not like 'D'";
            if (seq != "")
            {
                sql += " and training_group_seq = " + seq;
            }
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }



        public void DeleteTrainingGroup(int Sequence)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                //dbManager.AddParameters(2, "@updated_by", UpdateBy);
                //dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_groups SET RECORD_STATUS=@record_status WHERE training_group_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }




        public void InsertTrainingGroup(string training_group_descr,string inter, string created_by)
        {
            try
            {
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@training_group_descr", training_group_descr);
                dbManager.AddParameters(1, "@internal", inter);
                dbManager.AddParameters(2, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(3, "@created_by", created_by);
                dbManager.AddParameters(4, "@created_when", DateTime.Today);
                dbManager.Open();
                string sql;
                sql = "INSERT INTO hrm_training_groups (";
                sql += "training_group_descr";
                sql += ",internal";
                sql += ",created_by";
                sql += ",created_when";
                sql += ",RECORD_STATUS";
                sql += ") VALUES (";
                sql += "@training_group_descr";
                sql += ",@internal";              
                sql += ",@created_by";
                sql += ",@created_when";
                sql += ",@record_status)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void UpdateTrainingGroup(string Sequence, string training_group_descr,string inter, string updated_by)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@training_group_descr", training_group_descr);
                dbManager.AddParameters(2, "@internal", inter);
                dbManager.AddParameters(3, "@updated_by", updated_by);
                dbManager.AddParameters(4, "@updated_when", DateTime.Today);
                dbManager.Open();

                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_groups SET training_group_descr=@training_group_descr,internal=@internal,updated_by=@updated_by  WHERE training_group_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

        public DataSet ListTrainingTypes(string seq)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            string sql = "select tt.*,tt.training_type_seq as Sequence, tc.training_cat_descr"
                + ", case when tt.local = 1 then 'ภายในประเทศ' else 'ภายนอกประเทศ' end as n_local"
                + ", case when tt.internal = 1 then 'ภายในหน่วยงาน' else 'ภายนอกหน่วยงาน' end as n_internal ";
            sql += " from hrm_training_types as tt, hrm_training_type_categories as tc";
            sql += " where tt.record_status not like 'D' and tt.training_cat_seq = tc.training_cat_seq";
            if (seq != "")
            {
                sql += " and tt.training_type_seq = " + seq;
            }
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }



        public void DeleteTrainingTypes(int Sequence)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                //dbManager.AddParameters(2, "@updated_by", UpdateBy);
                //dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_types SET RECORD_STATUS=@record_status WHERE training_type_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }




        public void InsertTrainingTypes(string training_type_descr, string training_cat_seq, string created_by, string inter, string local)
        {
            try
            {
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@training_type_descr", training_type_descr);                                     dbManager.AddParameters(1, "@training_cat_seq", training_cat_seq);
                dbManager.AddParameters(2, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(3, "@created_by", created_by);
                dbManager.AddParameters(4, "@created_when", DateTime.Today);
                dbManager.AddParameters(5, "@internal", inter);
                dbManager.AddParameters(6, "@local", local);


                dbManager.Open();
                string sql;
                sql = "INSERT INTO hrm_training_types (";
                sql += "training_type_descr";
                sql += ",training_cat_seq";
                sql += ",created_by";
                sql += ",created_when";
                sql += ",internal";
                sql += ",local";                
                sql += ",RECORD_STATUS";
                sql += ") VALUES (";
                sql += "@training_type_descr";
                sql += ",@training_cat_seq";
                sql += ",@created_by";
                sql += ",@created_when";
                sql += ",@internal";
                sql += ",@local";                
                sql += ",@record_status)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void UpdateTrainingTypes(string Sequence, string training_type_descr, string training_cat_seq,string updated_by, string inter, string local )
        {
            try
            {
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@training_type_descr", training_type_descr);
                dbManager.AddParameters(2, "@training_cat_seq", training_cat_seq);
                dbManager.AddParameters(3, "@updated_by", updated_by);
                dbManager.AddParameters(4, "@updated_when", DateTime.Today);
                dbManager.AddParameters(5, "@internal", inter);
                dbManager.AddParameters(6, "@local", local);
                dbManager.Open();

                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_types SET training_type_descr=@training_type_descr,training_cat_seq=@training_cat_seq,updated_by=@updated_by,internal=@internal,local=@local  WHERE training_type_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

        public DataSet ListTrainingCurriculums(string seq)
        {
            dbManager.Open();
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();

            string sql = "select tc.training_cur_seq,tc.training_cur_descr,tc.details,tc.comparable_group,tc.training_type_seq,tc.default_total_hours,tc.training_cur_seq as Sequence,tt.training_type_descr "
                + " from hrm_training_curriculums tc"
                + " left join hrm_training_types tt on  tc.training_type_seq = tt.training_type_seq"
                + " where tc.RECORD_STATUS = @RecordStatus ";
            if (seq != "")
            {
                sql += " AND training_cur_seq = " + seq;
            }
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }
        public DataSet ListTrainingCurriculumsbySeq(string seq)
        {
            dbManager.Open();
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();

            string sql = "select tc.training_cur_seq,tc.training_cur_descr,tc.details,tc.comparable_group,tc.training_type_seq,tc.default_total_hours,tc.training_cur_seq as Sequence,tt.training_type_descr "
                + " from hrm_training_curriculums tc"
                + " left join hrm_training_types tt on  tc.training_type_seq = tt.training_type_seq"
                + " where tc.RECORD_STATUS = @RecordStatus ";

            if (seq != "")
            {
                sql += " AND training_cur_seq not like " + seq;
            }
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }


        public void DeleteTrainingCurriculums(int Sequence)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                //dbManager.AddParameters(2, "@updated_by", UpdateBy);
                //dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "update hrm_training_curriculums set record_status = @record_status WHERE training_cur_seq = @sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void InsertTrainingCurriculums(string training_cur_descr,string training_type_seq,string details, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                //dbManager.AddParameters(0, "@training_cur_seq", training_cur_seq);
                dbManager.AddParameters(0, "@training_cur_descr", training_cur_descr);
                dbManager.AddParameters(1, "@training_type_seq", training_type_seq);
                dbManager.AddParameters(2, "@details", details);
                dbManager.AddParameters(3, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(4, "@created_by", created_by);
                dbManager.AddParameters(5, "@created_when", DateTime.Today);


                
                string sql;
                sql = "INSERT INTO hrm_training_curriculums"
                + " (training_cur_descr, training_type_seq"
                + ", details ,created_by, created_when, RECORD_STATUS)"
                + " VALUES (@training_cur_descr, @training_type_seq"
                + ", @details, @created_by, @created_when, @record_status)";                

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }



        public void UpdateTrainingCurriculums(string Sequence, string training_cur_seq,
                        string training_cur_descr,string training_type_seq,string details, string updated_by)
        {
            try
            {
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@training_cur_seq", training_cur_seq);
                dbManager.AddParameters(1, "@sequence", Sequence);
                dbManager.AddParameters(2, "@training_cur_descr", training_cur_descr);
                dbManager.AddParameters(3, "@training_type_seq", training_type_seq);
                dbManager.AddParameters(4, "@details", details);
                dbManager.AddParameters(5, "@updated_by", updated_by);
                dbManager.AddParameters(6, "@updated_when", DateTime.Today);
                dbManager.Open();

                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_curriculums SET training_cur_seq = @training_cur_seq , training_cur_descr=@training_cur_descr ,training_type_seq=@training_type_seq,details=@details, updated_by=@updated_by  WHERE training_cur_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public Boolean chkDupTrainingCurriculums(string training_cur_seq)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "select * from hrm_training_curriculums"

                    + " where training_cur_seq = '" + training_cur_seq + "'";

                // + " and record_status='" + SystemInfo._ActiveRecordStatus + "'";
                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }



            }

            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }

            finally
            {
                dbManager.Dispose();
            }


        }
        public DataSet ListTrainingCurriculumsPrerequisites(string seq, string training_cur_seq)
        {
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
            dbManager.AddParameters(1, "@training_cur_seq", training_cur_seq);

            DataSet ds = new DataSet();
            dbManager.Open();

            string sql = "SELECT tp.prerequisite_training_cur_seq"
                            + " ,tp.prerequisite_training_cur_desc"
                            + " ,tp.training_cur_seq"
                            + " ,tp.prerequisite_seq AS Sequence"
                            + " ,tc.training_cur_descr"
                            + " FROM hrm_training_cur_prerequisites tp"
                            + " Inner Join hrm_training_curriculums  tc ON tp.training_cur_seq = tc.training_cur_seq"
                            + " where tp.record_status = @RecordStatus"
                            + " and tp.training_cur_seq = @training_cur_seq";
                if (seq != "")
                {
                    sql += " AND prerequisite_seq = " + seq;
                }

            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }

        public void DeleteTrainingCurriculumsPrerequisites(int Sequence)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                //dbManager.AddParameters(2, "@updated_by", UpdateBy);
                //dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_cur_prerequisites SET record_status = @record_status where prerequisite_seq = @sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void InsertTrainingCurriculumsPrerequisites(string training_cur_seq, string prerequisite_training_cur_seq, string prerequisite_training_cur_desc)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@training_cur_seq", Convert.ToInt32(training_cur_seq));
                dbManager.AddParameters(1, "@prerequisite_training_cur_seq", Convert.ToInt32(prerequisite_training_cur_seq));
                dbManager.AddParameters(2, "@prerequisite_training_cur_desc", prerequisite_training_cur_desc);
                dbManager.AddParameters(3, "@record_status", SystemInfo._ActiveRecordStatus);


                dbManager.Open();
                string sql;
                sql = "INSERT INTO hrm_training_cur_prerequisites (";
                sql += "training_cur_seq";
                sql += ",prerequisite_training_cur_seq";
                sql += ",prerequisite_training_cur_desc";
                sql += ",RECORD_STATUS";
                sql += ") VALUES (";
                sql += "@training_cur_seq";
                sql += ",@prerequisite_training_cur_seq";
                sql += ",@prerequisite_training_cur_desc";
                sql += ",@record_status)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }



        //public void UpdateTrainingCur(string Sequence, string prerequisite_group, string prerequisite_training_cur_seq, string updated_by)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(7);
        //        dbManager.AddParameters(0, "@training_cur_seq", training_cur_seq);
        //        dbManager.AddParameters(1, "@sequence", Sequence);
        //        dbManager.AddParameters(2, "@training_cur_descr", training_cur_descr);
        //        dbManager.AddParameters(3, "@training_type_seq", training_type_seq);
        //        dbManager.AddParameters(4, "@details", details);
        //        dbManager.AddParameters(5, "@updated_by", updated_by);
        //        dbManager.AddParameters(6, "@updated_when", DateTime.Today);
        //        dbManager.Open();

        //        dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_curriculums SET training_cur_seq = @training_cur_seq , training_cur_descr=@training_cur_descr ,training_type_seq=@training_type_seq,details=@details, updated_by=@updated_by  WHERE training_cur_seq=@sequence");
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}



        public DataSet ListTrainingCurriculumsSubject(string seq, string training_cur_seq)
        {
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
            dbManager.AddParameters(1, "@training_cur_seq", Convert.ToInt32(training_cur_seq));

            DataSet ds = new DataSet();
            dbManager.Open();
            string sql = "select tcs.training_cur_subj_seq as Sequence,ts.training_subject_descr from hrm_training_cur_subjects tcs Inner Join hrm_training_subjects  ts ON tcs.training_subject_seq = ts.training_subject_seq where tcs.RECORD_STATUS = @RecordStatus and tcs.training_cur_seq = @training_cur_seq";
            if (seq != "")
            {
                sql += "  AND tcs.training_cur_seq = " + seq;
            }
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }

        public void DeleteTrainingCurriculumsSubject(int Sequence)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                //dbManager.AddParameters(2, "@updated_by", UpdateBy);
                //dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_cur_subjects SET hrm_training_cur_subjects.record_status = @record_status where  hrm_training_cur_subjects.training_cur_subj_seq = @sequence");
                 
                 
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void InsertTrainingCurriculumsSubject(string training_cur_seq, string training_subject_seq)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@training_cur_seq", training_cur_seq);
                dbManager.AddParameters(1, "@training_subject_seq", training_subject_seq);
                dbManager.AddParameters(2, "@record_status", SystemInfo._ActiveRecordStatus);


                dbManager.Open();
                string sql;
                sql = "INSERT INTO hrm_training_cur_subjects (";
                sql += "training_cur_seq";
                sql += ",training_subject_seq";
                sql += ",RECORD_STATUS";
                sql += ") VALUES (";
                sql += "@training_cur_seq";
                sql += ",@training_subject_seq";
                sql += ",@record_status)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

        public DataSet ListTrainingCurriculumsCompare(string seq, string training_cur_seq)
        {
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
            dbManager.AddParameters(1, "@training_cur_seq", Convert.ToInt32(training_cur_seq));

            DataSet ds = new DataSet();
            dbManager.Open();
            string sql = "SELECT tcc.training_cur_compare_seq"
                        + " ,tcc.training_cur_seq"
                        + " ,tcc.compare_group_seq"
                        + " ,tcc.training_cur_compare_seq AS Sequence"
                        + " ,tg.compare_group_descr"
                        + " ,tg.compare_group_code"
                        + " from hrm_training_cur_compare tcc"
                        + " Inner Join hrm_training_compare_groups tg ON tcc.compare_group_seq = tg.compare_group_seq"
                        + " where tcc.RECORD_STATUS = @RecordStatus "
                        + " and tcc.training_cur_seq = @training_cur_seq";
            if (seq != "")
            {
                sql += " AND training_cur_seq = " + seq;
            }
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }

        public void DeleteTrainingCurriculumsCompare(int Sequence)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                //dbManager.AddParameters(2, "@updated_by", UpdateBy);
                //dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_cur_compare set hrm_training_cur_compare.record_status = @record_status where hrm_training_cur_compare.training_cur_compare_seq = @sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

        public void InsertTrainingCurriculumsCompare(string training_cur_seq, string compare_group_seq)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@training_cur_seq", training_cur_seq);
                dbManager.AddParameters(1, "@compare_group_seq", compare_group_seq);
                dbManager.AddParameters(2, "@record_status", SystemInfo._ActiveRecordStatus);


                dbManager.Open();
                string sql;
                sql = "INSERT INTO hrm_training_cur_compare (";
                sql += "training_cur_seq";
                sql += ",compare_group_seq";
                sql += ",RECORD_STATUS";
                sql += ") VALUES (";
                sql += "@training_cur_seq";
                sql += ",@compare_group_seq";
                sql += ",@record_status)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

        public bool chkDuptraining_cur_prerequisites(string training_cur_seq, string prerequisite_training_cur_seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@training_cur_seq", Convert.ToInt32(training_cur_seq));
                dbManager.AddParameters(1, "@prerequisite_training_cur_seq", Convert.ToInt32(prerequisite_training_cur_seq));
                dbManager.AddParameters(2, "@record_status", SystemInfo._DeleteRecordStatus);

                string sql;
                sql = "SELECT tp.prerequisite_training_cur_seq,tp.training_cur_seq FROM hrm_training_cur_prerequisites tp"
                    + " where tp.record_status not like @record_status"
                    + " AND tp.training_cur_seq = @training_cur_seq"
                    + " AND tp.prerequisite_training_cur_seq = @prerequisite_training_cur_seq";

                // + " and record_status='" + SystemInfo._ActiveRecordStatus + "'";
                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }

            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        //public DataSet ListTrainingSubject(string seq)
        //{
        //    dbManager.CreateParameters(1);
        //    dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

        //    DataSet ds = new DataSet();
        //    dbManager.Open();
        //    string sql = "select *,training_subject_seq as Sequence from hrm_training_subjects";
        //    sql += " where record_status not like 'D'";
        //    if (seq != "")
        //    {
        //        sql += " and training_subject_seq = " + seq;
        //    }
        //    ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //    dbManager.Dispose();
        //    return ds;
        //}



        //public void DeleteTrainingSubject(int Sequence, string UpdateBy)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(4);
        //        dbManager.AddParameters(0, "@sequence", Sequence);
        //        dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
        //        dbManager.AddParameters(2, "@updated_by", UpdateBy);
        //        dbManager.AddParameters(3, "@updated_when", DateTime.Today);

        //        dbManager.Open();
        //        dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_subjects SET RECORD_STATUS=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE training_subject_seq=@sequence");
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}




        //public void InsertTrainingSubject(string training_subject_descr, string CreatedBy)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(4);
        //        dbManager.AddParameters(0, "@training_subject_descr", training_subject_descr);
        //        dbManager.AddParameters(1, "@record_status", SystemInfo._ActiveRecordStatus);
        //        dbManager.AddParameters(2, "@CreatedBy", CreatedBy);
        //        dbManager.AddParameters(3, "@CreatedWhen", DateTime.Today);

        //        dbManager.Open();
        //        string sql;
        //        sql = "INSERT INTO hrm_training_subjects (";
        //        sql += "training_subject_descr";
        //        sql += ",record_status";
        //        sql += ",created_by";
        //        sql += ",created_when";
        //        sql += ") VALUES (";
        //        sql += "@training_subject_descr";
        //        sql += ",@record_status";
        //        sql += ",@CreatedBy";
        //        sql += ",@CreatedWhen)";

        //        dbManager.ExecuteNonQuery(CommandType.Text, sql);
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}


        //public void UpdateTrainingSubject(string Sequence, string training_subject_descr, string UpdateBy)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(4);
        //        dbManager.AddParameters(0, "@sequence", Sequence);
        //        dbManager.AddParameters(1, "@training_subject_descr", training_subject_descr);
        //        dbManager.AddParameters(2, "@updated_by", UpdateBy);
        //        dbManager.AddParameters(3, "@updated_when", DateTime.Today);
        //        dbManager.Open();

        //        dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_subjects SET training_subject_descr=@training_subject_descr,updated_by=@updated_by, updated_when=@updated_when WHERE training_subject_seq=@sequence");
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}



        //public DataSet ListTrainingSubjectDtls(string seq, string training_subject_seq)
        //{
        //    dbManager.CreateParameters(1);
        //    dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

        //    DataSet ds = new DataSet();
        //    dbManager.Open();
        //    string sql = "select *,training_subject_dtls_seq as Sequence from hrm_training_subjects_dtls";
        //    sql += " where record_status not like 'D' and training_subject_seq = " + training_subject_seq;
        //    if (seq != "")
        //    {
        //        sql += " and training_subject_seq = " + seq;
        //    }
        //    ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //    dbManager.Dispose();
        //    return ds;
        //}



        //public void DeleteTrainingSubjectDtls(int Sequence, string UpdateBy)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(4);
        //        dbManager.AddParameters(0, "@sequence", Sequence);
        //        dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
        //        dbManager.AddParameters(2, "@updated_by", UpdateBy);
        //        dbManager.AddParameters(3, "@updated_when", DateTime.Today);
        //        dbManager.Open();
        //        dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_subjects_dtls SET RECORD_STATUS=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE training_subject_dtls_seq=@sequence");
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}


        //public void InsertTrainingSubjectDtls(string training_subject_seq, string topic_descr, string detail, string CreatedBy)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(6);
        //        dbManager.AddParameters(0, "@training_subject_seq", training_subject_seq);
        //        dbManager.AddParameters(1, "@topic_descr", topic_descr);
        //        dbManager.AddParameters(2, "@detail", detail);
        //        dbManager.AddParameters(3, "@record_status", SystemInfo._ActiveRecordStatus);
        //        dbManager.AddParameters(4, "@CreatedBy", CreatedBy);
        //        dbManager.AddParameters(5, "@CreatedWhen", DateTime.Today);

        //        dbManager.Open();
        //        string sql;
        //        sql = "INSERT INTO hrm_training_subjects_dtls (";
        //        sql += "training_subject_seq";
        //        sql += ",topic_descr";
        //        sql += ",detail";
        //        sql += ",RECORD_STATUS";
        //        sql += ",created_by";
        //        sql += ",created_when";
        //        sql += ") VALUES (";
        //        sql += "@training_subject_seq";
        //        sql += ",@topic_descr";
        //        sql += ",@detail";
        //        sql += ",@record_status";
        //        sql += ",@CreatedBy";
        //        sql += ",@CreatedWhen)";

        //        dbManager.ExecuteNonQuery(CommandType.Text, sql);
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}


        //public void UpdateTrainingSubjectDtls(string Sequence, string topic_descr, string detai, string UpdateBy)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(5);
        //        dbManager.AddParameters(0, "@sequence", Sequence);
        //        dbManager.AddParameters(1, "@topic_descr", topic_descr);
        //        dbManager.AddParameters(2, "@detail", detai);
        //        dbManager.AddParameters(3, "@updated_by", UpdateBy);
        //        dbManager.AddParameters(4, "@updated_when", DateTime.Today);
        //        dbManager.Open();

        //        dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_subjects_dtls SET topic_descr=@topic_descr, detail=@detail,updated_by=@updated_by, updated_when=@updated_when WHERE training_subject_dtls_seq=@sequence");
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}



        //public DataSet ListScholarshipType(string seq)
        //{
        //    dbManager.CreateParameters(1);
        //    dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

        //    DataSet ds = new DataSet();
        //    dbManager.Open();
        //    string sql = "SELECT scholar_type_seq as Sequence, SCHOLAR_TYPE_DESCR"
        //               + ", CASE WHEN LOCAL not like 0 THEN 'ในประเทศ' ELSE 'ต่างประเทศ' END AS LOCAL"
        //               + ", CASE WHEN EDU_TIME_TYPE not like 0 THEN 'ลาศึกษา' ELSE 'นอกเวลาทำงาน' END AS EDU_TIME_TYPE"
        //               + " FROM hrm_scholarship_types where record_status not like 'D'";
        //    if (seq != "")
        //    {
        //        sql += " and scholar_type_seq = " + seq;
        //    }
        //    ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //    dbManager.Dispose();
        //    return ds;
        //}



        //public void DeleteScholarshipType(int Sequence, string UpdateBy)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(4);
        //        dbManager.AddParameters(0, "@sequence", Sequence);
        //        dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
        //        dbManager.AddParameters(2, "@updated_by", UpdateBy);
        //        dbManager.AddParameters(3, "@updated_when", DateTime.Today);
        //        dbManager.Open();
        //        dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_scholarship_types SET RECORD_STATUS=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE scholar_type_seq=@sequence");
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}


        //public void InsertScholarshipType(string scholar_type_descr, string local, string CreatedBy)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(5);
        //        dbManager.AddParameters(0, "@scholar_type_descr", scholar_type_descr);
        //        dbManager.AddParameters(1, "@local", local);
        //        dbManager.AddParameters(2, "@record_status", SystemInfo._ActiveRecordStatus);
        //        dbManager.AddParameters(3, "@CreatedBy", CreatedBy);
        //        dbManager.AddParameters(4, "@CreatedWhen", DateTime.Today);

        //        dbManager.Open();
        //        string sql;
        //        sql = "INSERT INTO hrm_scholarship_types (";
        //        sql += "scholar_type_descr";
        //        sql += ",local";
        //        sql += ",record_status";
        //        sql += ",created_by";
        //        sql += ",created_when";
        //        sql += ") VALUES (";
        //        sql += "@scholar_type_descr";
        //        sql += ",@local";
        //        sql += ",@record_status";
        //        sql += ",@CreatedBy";
        //        sql += ",@CreatedWhen)";

        //        dbManager.ExecuteNonQuery(CommandType.Text, sql);
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}


        //public void UpdateScholarshipType(string Sequence, string scholar_type_descr, string local, string UpdateBy)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(5);
        //        dbManager.AddParameters(0, "@sequence", Sequence);
        //        dbManager.AddParameters(1, "@scholar_type_descr", scholar_type_descr);
        //        dbManager.AddParameters(2, "@local", local);
        //        dbManager.AddParameters(3, "@updated_by", UpdateBy);
        //        dbManager.AddParameters(4, "@updated_when", DateTime.Today);
        //        dbManager.Open();

        //        string sql = "UPDATE hrm_scholarship_types SET scholar_type_descr=@scholar_type_descr, local=@local,updated_by=@updated_by, updated_when=@updated_when WHERE scholar_type_seq=@sequence";

        //        dbManager.ExecuteNonQuery(CommandType.Text, sql);
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}


        //public DataSet ListScholarshipExpSupport(string seq)
        //{
        //    dbManager.CreateParameters(1);
        //    dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

        //    DataSet ds = new DataSet();
        //    dbManager.Open();
        //    string sql = "SELECT *, exp_seq as Sequence"
        //               + " FROM hrm_scholarships_expenses where record_status not like 'D'";
        //    if (seq != "")
        //    {
        //        sql += " and exp_seq = " + seq;
        //    }
        //    ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //    dbManager.Dispose();
        //    return ds;
        //}
        //public void DeleteScholarshipExpSupport(int Sequence, string UpdateBy)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(4);
        //        dbManager.AddParameters(0, "@sequence", Sequence);
        //        dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
        //        dbManager.AddParameters(2, "@updated_by", UpdateBy);
        //        dbManager.AddParameters(3, "@updated_when", DateTime.Today);
        //        dbManager.Open();
        //        dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_scholarships_expenses SET RECORD_STATUS=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE exp_seq=@sequence");
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}


        //public void InsertScholarshipExpSupport(string exp_descr, string CreatedBy)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(4);
        //        dbManager.AddParameters(0, "@exp_descr", exp_descr);
        //        dbManager.AddParameters(1, "@record_status", SystemInfo._ActiveRecordStatus);
        //        dbManager.AddParameters(2, "@CreatedBy", CreatedBy);
        //        dbManager.AddParameters(3, "@CreatedWhen", DateTime.Today);

        //        dbManager.Open();
        //        string sql;
        //        sql = "INSERT INTO hrm_scholarships_expenses (";
        //        sql += "exp_descr";
        //        sql += ",record_status";
        //        sql += ",created_by";
        //        sql += ",created_when";
        //        sql += ") VALUES (";
        //        sql += "@exp_descr";
        //        sql += ",@record_status";
        //        sql += ",@CreatedBy";
        //        sql += ",@CreatedWhen)";

        //        dbManager.ExecuteNonQuery(CommandType.Text, sql);
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}


        //public void UpdateScholarshipExpSupport(string Sequence, string exp_descr, string UpdateBy)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(4);
        //        dbManager.AddParameters(0, "@sequence", Sequence);
        //        dbManager.AddParameters(1, "@exp_descr", exp_descr);
        //        dbManager.AddParameters(2, "@updated_by", UpdateBy);
        //        dbManager.AddParameters(3, "@updated_when", DateTime.Today);
        //        dbManager.Open();

        //        string sql = "UPDATE hrm_scholarships_expenses SET exp_descr=@exp_descr, updated_by=@updated_by, updated_when=@updated_when WHERE exp_seq=@sequence";

        //        dbManager.ExecuteNonQuery(CommandType.Text, sql);
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}





        //public DataSet ListScholarshipInstitute(string seq)
        //{
        //    dbManager.CreateParameters(1);
        //    dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

        //    DataSet ds = new DataSet();
        //    dbManager.Open();
        //    string sql = "SELECT i.*,c.*, i.training_ins_seq as Sequence"
        //               + " FROM hrm_training_institutes as i, mas_countries as c where i.country_seq = c.country_seq and i.record_status not like 'D'";
        //    if (seq != "")
        //    {
        //        sql += " and training_ins_seq = " + seq;
        //    }
        //    ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //    dbManager.Dispose();
        //    return ds;
        //}
        //public void DeleteScholarshipInstitute(int Sequence, string UpdateBy)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(4);
        //        dbManager.AddParameters(0, "@sequence", Sequence);
        //        dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
        //        dbManager.AddParameters(2, "@updated_by", UpdateBy);
        //        dbManager.AddParameters(3, "@updated_when", DateTime.Today);
        //        dbManager.Open();
        //        dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_institutes SET RECORD_STATUS=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE training_ins_seq=@sequence");
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}


        //public void InsertScholarshipInstitute(string training_ins_descr, string country_seq, string CreatedBy)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(5);
        //        dbManager.AddParameters(0, "@training_ins_descr", training_ins_descr);
        //        dbManager.AddParameters(1, "@country_seq", country_seq);
        //        dbManager.AddParameters(2, "@record_status", SystemInfo._ActiveRecordStatus);
        //        dbManager.AddParameters(3, "@CreatedBy", CreatedBy);
        //        dbManager.AddParameters(4, "@CreatedWhen", DateTime.Today);

        //        dbManager.Open();
        //        string sql;
        //        sql = "INSERT INTO hrm_training_institutes (";
        //        sql += "training_ins_descr";
        //        sql += ",country_seq";
        //        sql += ",record_status";
        //        sql += ",created_by";
        //        sql += ",created_when";
        //        sql += ") VALUES (";
        //        sql += "@training_ins_descr";
        //        sql += ",@country_seq";
        //        sql += ",@record_status";
        //        sql += ",@CreatedBy";
        //        sql += ",@CreatedWhen)";

        //        dbManager.ExecuteNonQuery(CommandType.Text, sql);
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}


        //public void UpdateScholarshipInstitute(string Sequence, string training_ins_descr, string country_seq, string UpdateBy)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(5);
        //        dbManager.AddParameters(0, "@sequence", Sequence);
        //        dbManager.AddParameters(1, "@training_ins_descr", training_ins_descr);
        //        dbManager.AddParameters(2, "@country_seq", country_seq);
        //        dbManager.AddParameters(3, "@updated_by", UpdateBy);
        //        dbManager.AddParameters(4, "@updated_when", DateTime.Today);
        //        dbManager.Open();

        //        string sql = "UPDATE hrm_training_institutes SET training_ins_descr=@training_ins_descr, country_seq=@country_seq, updated_by=@updated_by, updated_when=@updated_when WHERE training_ins_seq=@sequence";

        //        dbManager.ExecuteNonQuery(CommandType.Text, sql);
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}



        //public DataSet ListTrainers(string seq)
        //{
        //    dbManager.CreateParameters(1);
        //    dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

        //    DataSet ds = new DataSet();
        //    dbManager.Open();
        //    string sql = "select trainer_seq as Sequence,t.*"
        //    + ",case when t.emp_seq is null then p.prefix_name else p1.prefix_name end as  prefix_name"
        //    + ",case when t.emp_seq is null then t.first_name_local else e.first_name end as  first_name"
        //    + ",case  when t.emp_seq is null then t.last_name_local else e.last_name end as last_name"
        //        //     + ",case when t.emp_seq is null then t.address else a.address_descr end as address2"
        //    + ",case when t.district_seq is null then '' else d.district_name  end as district"
        //    + ",CONCAT(case  when t.emp_seq is null then p.prefix_name else p1.prefix_name end, case  when t.emp_seq is null then t.first_name_local else e.first_name end,' ', case  when t.emp_seq is null then t.last_name_local else e.last_name end ) full_name"

        //    + " FROM hrm_trainers t left join hrm_ps_employees e on t.emp_seq =  e.emp_seq"
        //    + " left join mas_name_prefixes p on t.name_prefix_seq = p.name_prefix_seq"
        //        //      + " left join hrm_ps_emp_address a on t.emp_seq = a.emp_seq and a.address_type_seq = 1"
        //    + " left join mas_name_prefixes p1 on e.name_prefix_seq = p1.name_prefix_seq"
        //    + " left join mas_districts d on t.district_seq = d.district_seq";

        //    if (seq != "")
        //    {
        //        sql += " where t.trainer_seq = " + seq;
        //    }
        //    ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //    dbManager.Dispose();
        //    return ds;
        //}

        //public void InsertTrainers(string empSta, string emp_seq, string name_prefix_seq, string first_name_local, string last_name_local, string address, string district_seq, string tel, string postal_code, string active, string CreatedBy)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(12);
        //        dbManager.AddParameters(0, "@emp_seq", emp_seq);
        //        dbManager.AddParameters(1, "@name_prefix_seq", name_prefix_seq);
        //        dbManager.AddParameters(2, "@first_name_local", first_name_local);
        //        dbManager.AddParameters(3, "@last_name_local", last_name_local);
        //        dbManager.AddParameters(4, "@address", address);
        //        dbManager.AddParameters(5, "@district_seq", district_seq);
        //        dbManager.AddParameters(6, "@tel", tel);
        //        dbManager.AddParameters(7, "@postal_code", postal_code);
        //        dbManager.AddParameters(8, "@active", active);
        //        dbManager.AddParameters(9, "@record_status", SystemInfo._ActiveRecordStatus);
        //        dbManager.AddParameters(10, "@CreatedBy", CreatedBy);
        //        dbManager.AddParameters(11, "@CreatedWhen", DateTime.Today);

        //        dbManager.Open();
        //        string sql;
        //        sql = "INSERT INTO hrm_trainers (";
        //        sql += "active";
        //        if (empSta != "in")
        //        {
        //            sql += ",name_prefix_seq";
        //            sql += ",first_name_local";
        //            sql += ",last_name_local";
        //            sql += ",address";
        //            sql += ",district_seq";
        //            sql += ",tel";
        //            sql += ",postal_code";
        //        }
        //        else
        //        {
        //            sql += ",emp_seq";
        //        }
        //        sql += ",record_status";
        //        sql += ",created_by";
        //        sql += ",created_when";
        //        sql += ") VALUES (";
        //        sql += "@active";
        //        if (empSta != "in")
        //        {
        //            sql += ",@name_prefix_seq";
        //            sql += ",@first_name_local";
        //            sql += ",@last_name_local";
        //            sql += ",@address";
        //            sql += ",@district_seq";
        //            sql += ",@tel";
        //            sql += ",@postal_code";
        //        }
        //        else
        //        {
        //            sql += ",emp_seq";
        //        }
        //        sql += ",@record_status";
        //        sql += ",@CreatedBy";
        //        sql += ",@CreatedWhen)";

        //        dbManager.ExecuteNonQuery(CommandType.Text, sql);
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}


        //public void UpdateTrainers(string empSta, string Sequence, string emp_seq, string name_prefix_seq, string first_name_local, string last_name_local, string address, string district_seq, string tel, string postal_code, string active, string UpdateBy)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(13);
        //        dbManager.AddParameters(0, "@emp_seq", emp_seq);
        //        dbManager.AddParameters(1, "@name_prefix_seq", name_prefix_seq);
        //        dbManager.AddParameters(2, "@first_name_local", first_name_local);
        //        dbManager.AddParameters(3, "@last_name_local", last_name_local);
        //        dbManager.AddParameters(4, "@address", address);
        //        dbManager.AddParameters(5, "@district_seq", district_seq);
        //        dbManager.AddParameters(6, "@tel", tel);
        //        dbManager.AddParameters(7, "@postal_code", postal_code);
        //        dbManager.AddParameters(8, "@active", active);
        //        dbManager.AddParameters(9, "@record_status", SystemInfo._ActiveRecordStatus);
        //        dbManager.AddParameters(10, "@updated_by", UpdateBy);
        //        dbManager.AddParameters(11, "@updated_when", DateTime.Today);
        //        dbManager.AddParameters(12, "@Sequence", Sequence);

        //        dbManager.Open();
        //        string sql;

        //        if (empSta != "in")
        //        {
        //            sql = "UPDATE hrm_trainers SET"
        //            + " name_prefix_seq=@name_prefix_seq"
        //            + ", first_name_local=@first_name_local"
        //            + ", last_name_local=@last_name_local"
        //            + ", address=@address";
        //            if (district_seq != "")
        //            {
        //                sql += ", district_seq=@district_seq";
        //            }
        //            sql += ", tel=@tel"
        //            + ", postal_code=@postal_code"
        //            + ", active=@active"
        //            + ", updated_by=@updated_by, updated_when=@updated_when"
        //            + " WHERE trainer_seq=@Sequence";
        //        }
        //        else
        //        {
        //            sql = "UPDATE hrm_trainers SET"
        //               + "emp_seq=@emp_seq"
        //               + ", active=@active"
        //               + ", updated_by=@updated_by, updated_when=@updated_when"
        //               + " WHERE trainer_seq=@Sequence";
        //        }

        //        dbManager.ExecuteNonQuery(CommandType.Text, sql);
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}
        //public void DeleteTrainers(int Sequence, string UpdateBy)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(4);
        //        dbManager.AddParameters(0, "@sequence", Sequence);
        //        dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
        //        dbManager.AddParameters(2, "@updated_by", UpdateBy);
        //        dbManager.AddParameters(3, "@updated_when", DateTime.Today);
        //        dbManager.Open();
        //        dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_trainers SET RECORD_STATUS=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE trainer_seq=@sequence");
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}


        //public DataSet ListScholarshipFunds(string seq)
        //{
        //    dbManager.CreateParameters(1);
        //    dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

        //    DataSet ds = new DataSet();
        //    dbManager.Open();
        //    string sql = "select *,scholar_fund_seq as Sequence from hrm_scholarship_funds";
        //    sql += " where record_status not like 'D'";
        //    if (seq != "")
        //    {
        //        sql += " and scholar_fund_seq = " + seq;
        //    }
        //    ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //    dbManager.Dispose();
        //    return ds;
        //}



        //public void DeleteScholarshipFunds(int Sequence, string UpdateBy)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(4);
        //        dbManager.AddParameters(0, "@sequence", Sequence);
        //        dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
        //        dbManager.AddParameters(2, "@updated_by", UpdateBy);
        //        dbManager.AddParameters(3, "@updated_when", DateTime.Today);

        //        dbManager.Open();
        //        dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_scholarship_funds SET RECORD_STATUS=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE scholar_fund_seq=@sequence");
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}




        //public void InsertScholarshipFunds(string scholar_fund_descr, string CreatedBy)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(4);
        //        dbManager.AddParameters(0, "@scholar_fund_descr", scholar_fund_descr);
        //        dbManager.AddParameters(1, "@record_status", SystemInfo._ActiveRecordStatus);
        //        dbManager.AddParameters(2, "@CreatedBy", CreatedBy);
        //        dbManager.AddParameters(3, "@CreatedWhen", DateTime.Today);

        //        dbManager.Open();
        //        string sql;
        //        sql = "INSERT INTO hrm_scholarship_funds (";
        //        sql += "scholar_fund_descr";
        //        sql += ",record_status";
        //        sql += ",created_by";
        //        sql += ",created_when";
        //        sql += ") VALUES (";
        //        sql += "@scholar_fund_descr";
        //        sql += ",@record_status";
        //        sql += ",@CreatedBy";
        //        sql += ",@CreatedWhen)";

        //        dbManager.ExecuteNonQuery(CommandType.Text, sql);
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}


        //public void UpdateScholarshipFunds(string Sequence, string scholar_fund_descr, string UpdateBy)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(4);
        //        dbManager.AddParameters(0, "@sequence", Sequence);
        //        dbManager.AddParameters(1, "@scholar_fund_descr", scholar_fund_descr);
        //        dbManager.AddParameters(2, "@updated_by", UpdateBy);
        //        dbManager.AddParameters(3, "@updated_when", DateTime.Today);
        //        dbManager.Open();

        //        dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_scholarship_funds SET scholar_fund_descr=@scholar_fund_descr,updated_by=@updated_by, updated_when=@updated_when WHERE scholar_fund_seq=@sequence");
        //        dbManager.Dispose();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}

        //public DataSet ListMasDistricts(string seq)
        //{
        //    dbManager.CreateParameters(1);
        //    dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

        //    DataSet ds = new DataSet();
        //    dbManager.Open();
        //    string sql = "SELECT *, exp_seq as Sequence"
        //               + " FROM hrm_scholarships_expenses where record_status not like 'D'";
        //    if (seq != "")
        //    {
        //        sql += " and exp_seq = " + seq;
        //    }
        //    ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //    dbManager.Dispose();
        //    return ds;
        //}
        public DataSet ListTrainingSubject(string seq)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            string sql = "select *,training_subject_seq as Sequence from hrm_training_subjects";
            sql += " where record_status not like 'D'";
            if (seq != "")
            {
                sql += " and training_subject_seq = " + seq;
            }
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }



        public void DeleteTrainingSubject(int Sequence, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_subjects SET RECORD_STATUS=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE training_subject_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }




        public void InsertTrainingSubject(string training_subject_descr, string CreatedBy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@training_subject_descr", training_subject_descr);
                dbManager.AddParameters(1, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(2, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(3, "@CreatedWhen", DateTime.Today);

                dbManager.Open();
                string sql;
                sql = "INSERT INTO hrm_training_subjects (";
                sql += "training_subject_descr";
                sql += ",record_status";
                sql += ",created_by";
                sql += ",created_when";
                sql += ") VALUES (";
                sql += "@training_subject_descr";
                sql += ",@record_status";
                sql += ",@CreatedBy";
                sql += ",@CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void UpdateTrainingSubject(string Sequence, string training_subject_descr, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@training_subject_descr", training_subject_descr);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();

                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_subjects SET training_subject_descr=@training_subject_descr,updated_by=@updated_by, updated_when=@updated_when WHERE training_subject_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }



        public DataSet ListTrainingSubjectDtls(string seq, string training_subject_seq)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            string sql = "select *,training_subject_dtls_seq as Sequence from hrm_training_subjects_dtls";
            sql += " where record_status not like 'D' and training_subject_seq = " + training_subject_seq;
            if (seq != "")
            {
                sql += " and training_subject_seq = " + seq;
            }
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }



        public void DeleteTrainingSubjectDtls(int Sequence, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_subjects_dtls SET record_status=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE training_subject_dtls_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void InsertTrainingSubjectDtls(string training_subject_seq, string topic_descr, string detail, string CreatedBy)
        {
            try
            {
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@training_subject_seq", training_subject_seq);
                dbManager.AddParameters(1, "@topic_descr", topic_descr);
                dbManager.AddParameters(2, "@detail", detail);
                dbManager.AddParameters(3, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(4, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(5, "@CreatedWhen", DateTime.Today);

                dbManager.Open();
                string sql;
                sql = "INSERT INTO hrm_training_subjects_dtls (";
                sql += "training_subject_seq";
                sql += ",topic_descr";
                sql += ",detail";
                sql += ",RECORD_STATUS";
                sql += ",created_by";
                sql += ",created_when";
                sql += ") VALUES (";
                sql += "@training_subject_seq";
                sql += ",@topic_descr";
                sql += ",@detail";
                sql += ",@record_status";
                sql += ",@CreatedBy";
                sql += ",@CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void UpdateTrainingSubjectDtls(string Sequence, string topic_descr, string detai, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@topic_descr", topic_descr);
                dbManager.AddParameters(2, "@detail", detai);
                dbManager.AddParameters(3, "@updated_by", UpdateBy);
                dbManager.AddParameters(4, "@updated_when", DateTime.Today);
                dbManager.Open();

                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_subjects_dtls SET topic_descr=@topic_descr, detail=@detail,updated_by=@updated_by, updated_when=@updated_when WHERE training_subject_dtls_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }



        public DataSet ListScholarshipType(string seq)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            string sql = "SELECT scholar_type_seq as Sequence, SCHOLAR_TYPE_DESCR"
                       + ", CASE WHEN LOCAL not like 0 THEN 'ในประเทศ' ELSE 'ต่างประเทศ' END AS LOCAL"
                       + ", CASE WHEN EDU_TIME_TYPE not like 0 THEN 'ลาศึกษา' ELSE 'นอกเวลาทำงาน' END AS EDU_TIME_TYPE"
                       + " FROM hrm_scholarship_types where record_status not like 'D'";
            if (seq != "")
            {
                sql += " and scholar_type_seq = " + seq;
            }
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }



        public void DeleteScholarshipType(int Sequence, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_scholarship_types SET RECORD_STATUS=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE scholar_type_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void InsertScholarshipType(string scholar_type_descr, string local, string CreatedBy)
        {
            try
            {
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@scholar_type_descr", scholar_type_descr);
                dbManager.AddParameters(1, "@local", local);
                dbManager.AddParameters(2, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(3, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(4, "@CreatedWhen", DateTime.Today);

                dbManager.Open();
                string sql;
                sql = "INSERT INTO hrm_scholarship_types (";
                sql += "scholar_type_descr";
                sql += ",local";
                sql += ",record_status";
                sql += ",created_by";
                sql += ",created_when";
                sql += ") VALUES (";
                sql += "@scholar_type_descr";
                sql += ",@local";
                sql += ",@record_status";
                sql += ",@CreatedBy";
                sql += ",@CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void UpdateScholarshipType(string Sequence, string scholar_type_descr, string local, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@scholar_type_descr", scholar_type_descr);
                dbManager.AddParameters(2, "@local", local);
                dbManager.AddParameters(3, "@updated_by", UpdateBy);
                dbManager.AddParameters(4, "@updated_when", DateTime.Today);
                dbManager.Open();

                string sql = "UPDATE hrm_scholarship_types SET scholar_type_descr=@scholar_type_descr, local=@local,updated_by=@updated_by, updated_when=@updated_when WHERE scholar_type_seq=@sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public DataSet ListScholarshipExpSupport(string seq)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            string sql = "SELECT *, exp_seq as Sequence"
                       + " FROM hrm_scholarships_expenses where record_status not like 'D'";
            if (seq != "")
            {
                sql += " and exp_seq = " + seq;
            }
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }
        public void DeleteScholarshipExpSupport(int Sequence, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_scholarships_expenses SET RECORD_STATUS=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE exp_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void InsertScholarshipExpSupport(string exp_descr, string CreatedBy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@exp_descr", exp_descr);
                dbManager.AddParameters(1, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(2, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(3, "@CreatedWhen", DateTime.Today);

                dbManager.Open();
                string sql;
                sql = "INSERT INTO hrm_scholarships_expenses (";
                sql += "exp_descr";
                sql += ",record_status";
                sql += ",created_by";
                sql += ",created_when";
                sql += ") VALUES (";
                sql += "@exp_descr";
                sql += ",@record_status";
                sql += ",@CreatedBy";
                sql += ",@CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void UpdateScholarshipExpSupport(string Sequence, string exp_descr, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@exp_descr", exp_descr);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();

                string sql = "UPDATE hrm_scholarships_expenses SET exp_descr=@exp_descr, updated_by=@updated_by, updated_when=@updated_when WHERE exp_seq=@sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }



        public DataSet ListScholarshipInstitute(string seq)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            string sql = "SELECT i.*,c.*, i.training_ins_seq as Sequence"
                       + " FROM hrm_training_institutes as i, mas_countries as c where i.country_seq = c.country_seq and i.record_status not like 'D'";
            if (seq != "")
            {
                sql += " and training_ins_seq = " + seq;
            }
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }
        public void DeleteScholarshipInstitute(int Sequence, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_training_institutes SET RECORD_STATUS=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE training_ins_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void InsertScholarshipInstitute(string training_ins_descr, string country_seq, string CreatedBy)
        {
            try
            {
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@training_ins_descr", training_ins_descr);
                dbManager.AddParameters(1, "@country_seq", country_seq);
                dbManager.AddParameters(2, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(3, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(4, "@CreatedWhen", DateTime.Today);

                dbManager.Open();
                string sql;
                sql = "INSERT INTO hrm_training_institutes (";
                sql += "training_ins_descr";
                sql += ",country_seq";
                sql += ",record_status";
                sql += ",created_by";
                sql += ",created_when";
                sql += ") VALUES (";
                sql += "@training_ins_descr";
                sql += ",@country_seq";
                sql += ",@record_status";
                sql += ",@CreatedBy";
                sql += ",@CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void UpdateScholarshipInstitute(string Sequence, string training_ins_descr, string country_seq, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@training_ins_descr", training_ins_descr);
                dbManager.AddParameters(2, "@country_seq", country_seq);
                dbManager.AddParameters(3, "@updated_by", UpdateBy);
                dbManager.AddParameters(4, "@updated_when", DateTime.Today);
                dbManager.Open();

                string sql = "UPDATE hrm_training_institutes SET training_ins_descr=@training_ins_descr, country_seq=@country_seq, updated_by=@updated_by, updated_when=@updated_when WHERE training_ins_seq=@sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }



        public DataSet ListTrainers(string seq)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            string sql = "select trainer_seq as Sequence,t.*"
            + ",case when t.emp_seq is null then p.prefix_name else p1.prefix_name end as  prefix_name"
            + ",case when t.emp_seq is null then t.first_name_local else e.first_name end as  first_name"
            + ",case  when t.emp_seq is null then t.last_name_local else e.last_name end as last_name"
                //     + ",case when t.emp_seq is null then t.address else a.address_descr end as address2"
            + ",case when t.district_seq is null then '' else d.district_name  end as district"
            + ",CONCAT(case  when t.emp_seq is null then p.prefix_name else p1.prefix_name end, case  when t.emp_seq is null then t.first_name_local else e.first_name end,' ', case  when t.emp_seq is null then t.last_name_local else e.last_name end ) full_name"

            + " FROM hrm_trainers t left join hrm_ps_employees e on t.emp_seq =  e.emp_seq"
            + " left join mas_name_prefixes p on t.name_prefix_seq = p.name_prefix_seq"
                //      + " left join hrm_ps_emp_address a on t.emp_seq = a.emp_seq and a.address_type_seq = 1"
            + " left join mas_name_prefixes p1 on e.name_prefix_seq = p1.name_prefix_seq"
            + " left join mas_districts d on t.district_seq = d.district_seq"
            + " where t.record_status not like 'D'";

            if (seq != "")
            {
                sql += " and t.trainer_seq = " + seq;
            }
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }

        public void InsertTrainers(string empSta, string emp_seq, string name_prefix_seq, string first_name_local, string last_name_local, string address, string district_seq, string tel, string postal_code, string active, string CreatedBy)
        {
            try
            {
                dbManager.CreateParameters(12);
                dbManager.AddParameters(0, "@emp_seq", emp_seq);
                dbManager.AddParameters(1, "@name_prefix_seq", name_prefix_seq);
                dbManager.AddParameters(2, "@first_name_local", first_name_local);
                dbManager.AddParameters(3, "@last_name_local", last_name_local);
                dbManager.AddParameters(4, "@address", address);
                dbManager.AddParameters(5, "@district_seq", district_seq);
                dbManager.AddParameters(6, "@tel", tel);
                dbManager.AddParameters(7, "@postal_code", postal_code);
                dbManager.AddParameters(8, "@active", active);
                dbManager.AddParameters(9, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(10, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(11, "@CreatedWhen", DateTime.Today);

                dbManager.Open();
                string sql;
                sql = "INSERT INTO hrm_trainers (";
                sql += "active";
                if (empSta != "in")
                {
                    sql += ",name_prefix_seq";
                    sql += ",first_name_local";
                    sql += ",last_name_local";
                    sql += ",address";
                }
                else
                {
                    sql += ",emp_seq";
                }
                sql += ",district_seq";
                sql += ",tel";
                sql += ",postal_code";
                sql += ",record_status";
                sql += ",created_by";
                sql += ",created_when";
                sql += ") VALUES (";
                sql += "@active";
                if (empSta != "in")
                {
                    sql += ",@name_prefix_seq";
                    sql += ",@first_name_local";
                    sql += ",@last_name_local";
                    sql += ",@address";
                }
                else
                {
                    sql += ",@emp_seq";
                }
                sql += ",@district_seq";
                sql += ",@tel";
                sql += ",@postal_code";
                sql += ",@record_status";
                sql += ",@CreatedBy";
                sql += ",@CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void UpdateTrainers(string empSta, string Sequence, string emp_seq, string name_prefix_seq, string first_name_local, string last_name_local, string address, string district_seq, string tel, string postal_code, string active, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(13);
                dbManager.AddParameters(0, "@emp_seq", emp_seq);
                dbManager.AddParameters(1, "@name_prefix_seq", name_prefix_seq);
                dbManager.AddParameters(2, "@first_name_local", first_name_local);
                dbManager.AddParameters(3, "@last_name_local", last_name_local);
                dbManager.AddParameters(4, "@address", address);
                dbManager.AddParameters(5, "@district_seq", district_seq);
                dbManager.AddParameters(6, "@tel", tel);
                dbManager.AddParameters(7, "@postal_code", postal_code);
                dbManager.AddParameters(8, "@active", active);
                dbManager.AddParameters(9, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(10, "@updated_by", UpdateBy);
                dbManager.AddParameters(11, "@updated_when", DateTime.Today);
                dbManager.AddParameters(12, "@Sequence", Sequence);

                dbManager.Open();
                string sql;

                if (empSta != "in")
                {
                    sql = "UPDATE hrm_trainers SET"
                    + " name_prefix_seq=@name_prefix_seq"
                    + ", first_name_local=@first_name_local"
                    + ", last_name_local=@last_name_local"
                    + ", address=@address"
                    + ", district_seq=@district_seq"
                    + ", tel=@tel"
                    + ", postal_code=@postal_code"
                    + ", active=@active"
                    + ", updated_by=@updated_by, updated_when=@updated_when"
                    + " WHERE trainer_seq=@Sequence";
                }
                else
                {
                    sql = "UPDATE hrm_trainers SET"
                       + " emp_seq=@emp_seq"
                       + ", district_seq=@district_seq"
                       + ", tel=@tel"
                       + ", postal_code=@postal_code"
                       + ", active=@active"
                       + ", updated_by=@updated_by, updated_when=@updated_when"
                       + " WHERE trainer_seq=@Sequence";
                }

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }
        public void DeleteTrainers(int Sequence, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_trainers SET RECORD_STATUS=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE trainer_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public DataSet ListScholarshipFunds(string seq)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            string sql = "select *,scholar_fund_seq as Sequence from hrm_scholarship_funds";
            sql += " where record_status not like 'D'";
            if (seq != "")
            {
                sql += " and scholar_fund_seq = " + seq;
            }
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }



        public void DeleteScholarshipFunds(int Sequence, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_scholarship_funds SET RECORD_STATUS=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE scholar_fund_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }




        public void InsertScholarshipFunds(string scholar_fund_descr, string CreatedBy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@scholar_fund_descr", scholar_fund_descr);
                dbManager.AddParameters(1, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(2, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(3, "@CreatedWhen", DateTime.Today);

                dbManager.Open();
                string sql;
                sql = "INSERT INTO hrm_scholarship_funds (";
                sql += "scholar_fund_descr";
                sql += ",record_status";
                sql += ",created_by";
                sql += ",created_when";
                sql += ") VALUES (";
                sql += "@scholar_fund_descr";
                sql += ",@record_status";
                sql += ",@CreatedBy";
                sql += ",@CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void UpdateScholarshipFunds(string Sequence, string scholar_fund_descr, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@scholar_fund_descr", scholar_fund_descr);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();

                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_scholarship_funds SET scholar_fund_descr=@scholar_fund_descr,updated_by=@updated_by, updated_when=@updated_when WHERE scholar_fund_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

        public DataSet ListMasDistricts(string seq)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();

            string sql = "SELECT d.district_seq as Sequence"
                       + ", CONCAT(case when p.prov_seq = 1000 then 'แขวง' when p.prov_seq not like 1000 then 'ต.' end ,d.district_name,'  ',case when p.prov_seq = 1000 then 'เขต' when p.prov_seq not like 1000 then 'อ.' end, a.amp_name, '   จ.',p.prov_name   ) AS Address"
                       + " from mas_districts d inner join mas_amphurs a on d.amp_seq = a.amp_seq"
                       + " inner join mas_provinces p on a.prov_seq = p.prov_seq";
            if (seq != "")
            {
                sql += " where d.district_seq = '" + seq + "'";
            }
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }


    }
}
